﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Net.Mail;
using System.Data;
using System.Data.SQLite;

namespace PTCScheduler
{
    class ReminderEmails
    {
        //SQLite connection for the main window page
        private SQLiteConnection conn;

        //SQLite data dapter for main window page
        private SQLiteDataAdapter da;

        //SQLite database connection string
        private String connString = SystemQueries.dbConnString();

        //SQLite transaction object
        private SQLiteTransaction transaction;

        //Empty constructor.
        public ReminderEmails()
        {
        }

        public void sendReminderEmails()
        {
           //Gather details and send reminders based off of data set.
            DataSet ds = dsReminderEmails();

            //Only push emails if records are found with reminder status as 'NO'.
            if (ds.Tables[0].Rows.Count > 0)
            {
                //Pass dataset to initialization method.
                initiailizeEmailDetails(ds);

                //Update reminder statuses.
                updateReminderStatuses();
            }
        }

        //Retrieve the dataset for conferences scheduled for today but reminder emails not sent.
        private DataSet dsReminderEmails()
        {
            //Create connection.
            conn = new SQLiteConnection(connString);

            //Open connection
            conn.Open();

            //Retrieve the today's query string from the SystemQueries class. 
            String sql = SystemQueries.reminderEmailQueryString();

            //Create dataset to fill with the results of the query. 
            DataSet ds = new DataSet(); //System.Data assembly

            //User SQLiteDataAdapter to retrieve results. 
            da = new SQLiteDataAdapter(sql, connString);

            //Fill the dataset with the results. 
            da.Fill(ds);

            //Close connection.
            conn.Close();

            //Return dataset with information needed for emails.
            return ds;
        }

        //Add email details to the email automation reminder body builder and send emails to teachers.
        private void initiailizeEmailDetails(DataSet ds)
        {
            //Set variables to hold information.
            String CounselorNeeded;
            String Date;
            String Time;
            String Location;
            String CounselorName;
            String StudentID;
            String TeacherName;
            String emailDetails;

            //Search through dataset and set details.
            foreach(DataRow row in ds.Tables[0].Rows)
            {
                //Retrieve individual columns in row based off of column name.
                CounselorNeeded = (String)row["CN"];
                Date = ((String)row["Date and Time"]).Substring(0,11);
                Time = timeFormat(((String)row["Date and Time"]).Substring(11));
                Location = (String)row["Location"];
                CounselorName = (String)row["Counselor"];
                StudentID = Convert.ToString(row["Student ID"]);
                TeacherName = (String)row["Teacher"];

                //Create email automation object.
                EmailAutomation email = new EmailAutomation();

                //Build email details.
                emailDetails = email.buildReminderEmailDetailsString(CounselorNeeded,Date,Time,Location,CounselorName,StudentID);

                //Build email body.
                email.buildReminderEmailBody(TeacherName, emailDetails);

                //Make email TO teacher email.
                email.TO = (String)row["Teacher Contact"];

                //Send the email to the teacher with conference details.
                email.sendReminderEmail();
            }//End for loop
        }//End initialize email

        //Method to format time string to 12 hour span.
        private String timeFormat(String strTime)
        {
            //Create Date and Time object
            DateTime dt = DateTime.Parse(strTime);

            //Return desired formatted string.
            return dt.ToString("hh:mm tt");
        }

        //Method to return update reminder status string.
        private String updateReminderString()
        {
            return @"UPDATE conferences SET reminderStatus = 'YES' 
                        WHERE date BETWEEN datetime('" + System.DateTime.Today.ToString("yyyy-MM-dd") + " 00:00:00') and datetime ('" + System.DateTime.Today.ToString("yyyy-MM-dd") + " 23:00:00')";
        }

        //Method to update reminder statuses of conferences.
        private void updateReminderStatuses()
        {
            //Create connection.
            conn = new SQLiteConnection(connString);

            //Open connection
            conn.Open();

            //Set the SQLiteTransaction
            transaction = conn.BeginTransaction();

            //Create the SQLite command and set its properties
            SQLiteCommand command = new SQLiteCommand();
            command.Transaction = transaction;
            command.CommandText = this.updateReminderString();
            command.ExecuteNonQuery();

            //Commit the transaction
            transaction.Commit();
            transaction.Dispose();

            //Close connection.
            conn.Close();
        }
    }
}
